﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class panel_PurchaseOrder : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        public panel_PurchaseOrder()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();
            this.PurchaseOrder.DoubleClick += PurchaseOrder_DoubleClick;
            this.PurchaseOrder.PopupMenuShowing += PurchaseOrder_PopupMenuShowing;
            this.poContextMenu.ItemClicked += poContextMenu_ItemClicked;
        }

        void poContextMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.poContextMenu.Hide();            
            switch (e.ClickedItem.Name)
            {                
                case "addSelection":
                    frm_Add_PurchaseOrder add_Form = new frm_Add_PurchaseOrder();                    
                    add_Form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    callEditForm();
                    break;
                case "cancelSelection":
                    cancelPO();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void PurchaseOrder_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.poContextMenu.Show(MousePosition);
        }

        private void PurchaseOrder_DoubleClick(object sender, EventArgs e)
        {
            callEditForm();
        }

        private void callEditForm()
        {
            DataRow row = PurchaseOrder.GetFocusedDataRow();
            frm_Edit_PurchaseOrder editForm = new frm_Edit_PurchaseOrder();
            editForm.POID = row[0].ToString();
            if (editForm.LoadData())
            {
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot edit selected record. Please try again.");
            }
        }

        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT t1.ID, t1.InputDate, t2.SupplierName, t1.NameOfCommodity, t3.DeliveryTermName,"
                + " t4.PaymentName, t1.Remark, t1.Change, t1.Note, t1.Finished, t1.Paid, t1.Cancelled, t5.ShipToName"
                + " FROM [241_PO] AS t1"
                + " LEFT JOIN [222_Supplier] AS t2 ON t1.SupplierID=t2.SupplierID"
                + " LEFT JOIN [222_DeliveryTerm] AS t3 ON t1.DeliveryTermID=t3.DeliveryTermID"
                + " LEFT JOIN [222_Payment] AS t4 ON t1.PaymentID=t4.PaymentID"
                + " LEFT JOIN [222_ShipTo] AS t5 ON t1.ShipToCode=t5.ShipToCode"
                + " ORDER BY SUBSTRING(t1.ID, 5, 6) DESC";
            sourceDataSet = mySQLManagement.SQL_Query(sql);            
            if (sourceDataSet != null)
            {
                gridPurchaseOrder.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(string));
                newTable.Columns.Add("InputDate", typeof(DateTime));
                newTable.Columns.Add("SupplierID", typeof(string));
                newTable.Columns.Add("NameOfCommodity", typeof(string));
                newTable.Columns.Add("DeliveryTermID", typeof(string));
                newTable.Columns.Add("PaymentID", typeof(string));
                newTable.Columns.Add("Remark", typeof(string));
                newTable.Columns.Add("Change", typeof(string));
                newTable.Columns.Add("Note", typeof(string));
                newTable.Columns.Add("Finished", typeof(string));
                newTable.Columns.Add("Paid", typeof(string));
                newTable.Columns.Add("Cancelled", typeof(string));
                newTable.Columns.Add("ShipToCode", typeof(string));
                gridPurchaseOrder.DataSource = newTable;
            }
            PurchaseOrder.Columns[0].Caption = "POID";
            PurchaseOrder.Columns[1].Caption = "Input Date";
            PurchaseOrder.Columns[2].Caption = "Supplier ID";
            PurchaseOrder.Columns[3].Caption = "Name of Commodity";
            PurchaseOrder.Columns[4].Caption = "Delivery Point";
            PurchaseOrder.Columns[5].Caption = "Payment";
            PurchaseOrder.Columns[6].Caption = "Remark";
            PurchaseOrder.Columns[7].Caption = "Change";
            PurchaseOrder.Columns[8].Caption = "Note";
            PurchaseOrder.Columns[9].Caption = "Finished";
            PurchaseOrder.Columns[10].Caption = "Paid";
            PurchaseOrder.Columns[11].Caption = "Cancelled";
            PurchaseOrder.Columns[12].Caption = "Ship To";
            PurchaseOrder.BestFitColumns();
        }

        ///////////////
        /// Cancel PO
        ///////////////
        private void cancelPO()
        {
            DataRow row = PurchaseOrder.GetFocusedDataRow();
            string poid = row["ID"].ToString();
            if (MessageBox.Show("Really cancel?", "Confirm cancel", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                bool isFinished = checkPOFinished(poid);
                if (isFinished)
                {
                    MessageBox.Show("Cannot cancel. The POID " + poid + " has been finished.");
                }
                else
                {
                    int receiveQuantity = sumPartReceiveQuantity(poid);
                    if (receiveQuantity > 0)
                    {
                        MessageBox.Show("Cannot cencel. There are some received units for the POID " + poid);
                    }
                    else
                    {
                        if (cancelAllOrderNo(poid))
                        {
                            try
                            {
                                myConnection.Open();
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "UPDATE [241_PO] SET Cancelled=1, Finished=1"
                                    + " WHERE ID='" + poid + "' AND Finished=0";
                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                myConnection.Close();
                                MessageBox.Show("The POID " + poid + " has been canceled successfully.");
                            }
                            catch
                            {
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                                MessageBox.Show("Cannot cancel the POID " + poid + ". Please try again.");
                            }
                        }
                        else
                        {
                            MessageBox.Show("Cannot cancel the POID " + poid + ". Please try again.");
                        }
                    }
                }
            }
        }
        
        ////////////////////////////////////////////////////////
        /// Check Finished PO
        ////////////////////////////////////////////////////////
        private bool checkPOFinished(string poid)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT COUNT(ID) FROM [241_PO]"
                    + " WHERE ID='" + poid + "' AND Finished=1";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    int numberRecord = Int32.Parse(dataReader[0].ToString());
                    if (numberRecord > 0)
                    {
                        ret = true;
                    }
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get check finished for POID " + poid);
            }
            return ret;
        }

        //////////////////////////////////////////
        /// Sum quantity of Part Receive for POID
        //////////////////////////////////////////
        private int sumPartReceiveQuantity(string poid)
        {
            int ret = 0;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT SUM(Quantity) FROM [241_PartReceive] WHERE POID='" + poid + "'";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    if (!string.IsNullOrEmpty(dataReader[0].ToString()))
                    {
                        ret = Int32.Parse(dataReader[0].ToString());
                    }
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get sum quantity of PartReceive");
            }
            return ret;
        }

        ///////////////////////////////////////////
        /// Cancel all OrderNo for POID
        //////////////////////////////////////////
        private bool cancelAllOrderNo(string poid)
        {
            bool ret = true;
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "UPDATE [241_PODetails] SET Cancelled=1, Finished=1"
                    + " WHERE POID='" + poid + "' AND Finished=0";
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = false;
            }
            return ret;
        }

        private void poAdd_Click(object sender, EventArgs e)
        {
            frm_Add_PurchaseOrder add_Form = new frm_Add_PurchaseOrder();            
            add_Form.ShowDialog();
            LoadData();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void poUpdate_Click(object sender, EventArgs e)
        {
            callEditForm();
        }

        private void poCancel_Click(object sender, EventArgs e)
        {
            cancelPO();
        }
    }
}
